package com.gukun.springboot.tdengine.service.managerpoints;

import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.gukun.springboot.tdengine.dao.ManagerPointsInfoMapper;
import com.gukun.springboot.tdengine.dao.TDEngineDao;
import com.gukun.springboot.tdengine.domain.common.CommonResult;
import com.gukun.springboot.tdengine.domain.enums.TagType;
import com.gukun.springboot.tdengine.domain.exception.util.ServiceExceptionUtil;
import com.gukun.springboot.tdengine.domain.managetag.DbsManagetagInfo;
import com.gukun.springboot.tdengine.dto.ExportTableDto;
import com.gukun.springboot.tdengine.dto.ShowTableDto;
import com.gukun.springboot.tdengine.pojo.DBConfig;
import com.gukun.springboot.tdengine.pojo.TagInfo;
import com.gukun.springboot.tdengine.service.TDEngineService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.math.BigDecimal;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;

@Service
public class ManagerPointsInfoService {

    private Integer downloadCount = 0;
    private static final Logger log = LoggerFactory.getLogger(ManagerPointsInfoService.class);
    @Autowired
    private ManagerPointsInfoMapper managerPointsInfoMapper;

    @Autowired
    private TDEngineService tdEngineService;

    /**
     * 解析上传的文件
     * @param file
     * @return
     */
    public CommonResult<String> uploadFile(MultipartFile file, String stable, DBConfig dbConfig,boolean type) {
        //流要转换整文件
        File file1= mutipartFile(file);
        List<Map> array =null;
        try {
            String fileName =file1.getName().toLowerCase();
            Workbook work =null;
            if (fileName.toUpperCase().endsWith("XLSX")){
                work = new XSSFWorkbook(new FileInputStream(file1));
            }else if (fileName.toUpperCase().endsWith("XLS")){
                POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file1));
                work =new HSSFWorkbook(poifsFileSystem);
            }else{
                return CommonResult.error(500,"上传模板文件后缀存在问题");
            }
            array = read(work);
            if(array != null && array.size()>0){
                LambdaQueryWrapper<DbsManagetagInfo> wrapper = new LambdaQueryWrapper<>();
                wrapper.eq(DbsManagetagInfo::getDbName, dbConfig.getDbName());
                wrapper.eq(DbsManagetagInfo::getDbIp, dbConfig.getHost());
//                List<DbsManagetagInfo> allList = managerPointsInfoMapper.selectList(wrapper);
//                List<String> nameList = new ArrayList<>();
//                if(allList != null && allList.size()>0){
//                    for(DbsManagetagInfo dbsManagetagInfo : allList){
//                        nameList.add(dbsManagetagInfo.getTagName());
//                    }
//                }

                List<TagInfo> tagInfoList = new ArrayList<>();
                TagInfo tagInfo = null;
                for(Map<String,String> map  : array){
                    if (map != null && map.keySet().size()>0){
                        Iterator<String> its = map.keySet().iterator();
                        while (its.hasNext()){
                            //此处的key表示的是sheet页的名称
                            String sheetName = its.next();
                            Object val= map.get(sheetName);
                            if(val != null){
                                List<Map<String,String>> valList = (List<Map<String,String>>)val;
                                if(valList != null && valList.size()>0){
                                    DbsManagetagInfo dbsManagetagInfo = null;
                                    for(Map<String,String> valMap  : valList){
                                        dbsManagetagInfo = new DbsManagetagInfo();
                                        Iterator<String> its2 = valMap.keySet().iterator();
                                        while (its2.hasNext()){
                                            String key = its2.next();
                                            String columnVal= valMap.get(key);
                                            if(key.equalsIgnoreCase("表名")){
                                                dbsManagetagInfo.setTagName(columnVal);
                                            }else if(key.equalsIgnoreCase("描述")){
                                                dbsManagetagInfo.setTagDesc(columnVal);
                                            }else if(key.equalsIgnoreCase("场站")){
                                                dbsManagetagInfo.setTn(columnVal);
                                            }else if(key.equalsIgnoreCase("ta")){
                                                dbsManagetagInfo.setTa(columnVal);
                                            }else if(key.equalsIgnoreCase("小数位数")){
                                                byte b = 0;
                                                try{
                                                    if(StringUtils.isNotBlank(columnVal)){
                                                        b = Byte.parseByte(columnVal);
                                                    }
                                                }catch (Exception e){

                                                }
                                                dbsManagetagInfo.setPointNum(b);
                                            }else if(key.equalsIgnoreCase("单位")){
                                                dbsManagetagInfo.setSzUnit(columnVal);
                                            }else if(key.equalsIgnoreCase("量程上限")){
                                                float max = 100.0f;
                                                try{
                                                    if(StringUtils.isNotBlank(columnVal)){
                                                        max = Float.parseFloat(columnVal);
                                                    }
                                                }catch (Exception e){

                                                }
                                                dbsManagetagInfo.setMaxValue(max);
                                            }else if(key.equalsIgnoreCase("量程下限")){
                                                float min = 0.0f;
                                                try{
                                                    if(StringUtils.isNotBlank(columnVal)){
                                                        min = Float.parseFloat(columnVal);
                                                    }
                                                }catch (Exception e){

                                                }
                                                dbsManagetagInfo.setMinValue(min);
                                            }else if(key.equalsIgnoreCase("ta")){
                                                dbsManagetagInfo.setTa(columnVal);
                                            }else if (key.equalsIgnoreCase("类型")){
                                                if(StringUtils.isNotBlank(stable)){
                                                    if(stable.equalsIgnoreCase(columnVal)){
                                                        dbsManagetagInfo.setDataType(getDataType(columnVal));
                                                        dbsManagetagInfo.setTagType(getTagType(columnVal));
//                                                        dbsManagetagInfo.setTagDesc(columnVal);
                                                    }else{
                                                        dbsManagetagInfo = null;
                                                        break;
                                                    }
                                                }else{
                                                    dbsManagetagInfo.setDataType(getDataType(columnVal));
                                                    dbsManagetagInfo.setTagType(getTagType(columnVal));
//                                                    dbsManagetagInfo.setTagDesc(columnVal);
                                                }
                                            }
                                        }
                                        if(dbConfig!= null && dbsManagetagInfo != null && dbsManagetagInfo.getTagName() != null
                                                && dbsManagetagInfo.getDataType() != -1){
                                            dbsManagetagInfo.setDbName(dbConfig.getDbName());
                                            dbsManagetagInfo.setDbIp(dbConfig.getHost());
                                            dbsManagetagInfo.setTagStatue("未同步");
                                            //此处去掉mysql数据库
                                            tagInfo = new TagInfo();
                                            tagInfo.setTagName(dbsManagetagInfo.getTagName());
                                            TagType tagType = getEnumTagType(dbsManagetagInfo.getTagType());
                                            if(tagType != null){
                                                tagInfo.setTagDesc(dbsManagetagInfo.getTagDesc());
                                                tagInfo.setTagType(tagType);
                                                tagInfo.setPointNum(dbsManagetagInfo.getPointNum());
                                                tagInfo.setUnit(dbsManagetagInfo.getSzUnit());
                                                tagInfo.setMaxValue(dbsManagetagInfo.getMaxValue());
                                                tagInfo.setMinValue(dbsManagetagInfo.getMinValue());
                                                tagInfo.setTags(tagInfo.toString());
                                                tagInfo.setTi(tagInfo.toString());
                                                tagInfo.setTn(dbsManagetagInfo.getTn());
                                                tagInfo.setTa(dbsManagetagInfo.getTa());
                                                tagInfoList.add(tagInfo);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                TDEngineDao tdEngineDao = tdEngineService.getTDEngineDao(dbConfig.getHost(), dbConfig.getPort(), dbConfig.getUsername()
                        , dbConfig.getPassword(), dbConfig.getDbName());
                tdEngineDao.useDb(dbConfig.getDbName());
                if(type){
                    tdEngineDao.createTables(tagInfoList);
                }else{
                    tdEngineDao.deleteTables(tagInfoList);
                    System.out.println("删除结束");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return CommonResult.success("上传点表信息成功");
    }

    public DBConfig checkFileAndDbConfigInfo(MultipartFile file, String username,
                                             String password,String host,String dbName,String port) throws Exception{
        if(file == null || file.isEmpty()){
            throw ServiceExceptionUtil.exception0(500,"上传文件为空");
        }
        if(StringUtils.isBlank(host) || StringUtils.isBlank(port) || StringUtils.isBlank(username) || StringUtils.isBlank(password) ||
                StringUtils.isBlank(dbName)){
            throw ServiceExceptionUtil.exception0(500,"请连接TDengine数据库");
        }
        DBConfig dbConfig = new DBConfig(host,Integer.parseInt(port),username,password);
        dbConfig.setDbName(dbName);
        return dbConfig;
    }

    /**
     * 修改描述信息的ti字段
     * @param file
     * @param dbConfig
     * @return
     * @throws Exception
     */
    public boolean updateTagDescByFile(MultipartFile file, DBConfig dbConfig) throws Exception{
        File excelFile = mutipartFile(file);
        String fileName =excelFile.getName().toLowerCase();
        Workbook work =null;
        if (fileName.toUpperCase().endsWith("XLSX")){
            work = new XSSFWorkbook(new FileInputStream(excelFile));
        }else if (fileName.toUpperCase().endsWith("XLS")){
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(excelFile));
            work =new HSSFWorkbook(poifsFileSystem);
        }else{
            throw ServiceExceptionUtil.exception0(500,"上传模板文件后缀存在问题");
        }
        Sheet sheet = work.getSheetAt(0);
        Row row = null;
        int rowIndex = 1;
        HashMap<String,String> tagDescMap = new HashMap<>();
        while(true){
            row = sheet.getRow(rowIndex);
            if(row == null) {
                break;
            }
            Cell tagNameCell = row.getCell(0);
            String tagName = tagNameCell.getStringCellValue();
            if(StringUtils.isNotBlank(tagName)){
                String tagDesc = row.getCell(1) != null ? row.getCell(1).getStringCellValue():null;
                String type = row.getCell(2) != null ? row.getCell(2).getStringCellValue():null;
                String pointNum = row.getCell(5) != null ? String.valueOf(row.getCell(5).getNumericCellValue()):"0";
                byte pn = StringUtils.isNotBlank(pointNum)?new BigDecimal(pointNum).byteValue() :0;
                String szUnit = row.getCell(6) != null?row.getCell(6).getStringCellValue():null;
                String fmaxVal = row.getCell(7)!=null ? row.getCell(7).getStringCellValue():"100.0";
                float max = StringUtils.isNotBlank(fmaxVal)?Float.parseFloat(fmaxVal):0;// max
                String fminVal = row.getCell(8) != null ? row.getCell(8).getStringCellValue():"0.0";
                float min = StringUtils.isNotBlank(fminVal)?Float.parseFloat(fminVal):0;

                TagInfo tagInfo = new TagInfo();
                tagInfo.setTagName(tagName);
                TagType tagType = getEnumTagType(type);
                if(tagType != null){
                    tagInfo.setTagDesc(tagDesc);
                    tagInfo.setTagType(tagType);
                    tagInfo.setPointNum(pn);
                    tagInfo.setUnit(szUnit);
                    tagInfo.setMaxValue(max);
                    tagInfo.setMinValue(min);
                    tagDescMap.put(tagName.toLowerCase(),tagInfo.toString());
                }
            }
            rowIndex++;
        }
        tdEngineService.updateTagTi(dbConfig,tagDescMap);
        return true;
    }

    public boolean updateTagTnByFile(MultipartFile file, DBConfig dbConfig) throws Exception{
        File excelFile = mutipartFile(file);
        String fileName =excelFile.getName().toLowerCase();
        Workbook work =null;
        if (fileName.toUpperCase().endsWith("XLSX")){
            work = new XSSFWorkbook(new FileInputStream(excelFile));
        }else if (fileName.toUpperCase().endsWith("XLS")){
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(excelFile));
            work =new HSSFWorkbook(poifsFileSystem);
        }else{
            throw ServiceExceptionUtil.exception0(500,"上传模板文件后缀存在问题");
        }
        Sheet sheet = work.getSheetAt(0);
        Row row = null;
        int rowIndex = 1;

        Row firstRow = sheet.getRow(0);
        int tnIndex = -1;
        Short lastCellNum = firstRow.getLastCellNum();
        for(int i = 0;i<lastCellNum;i++){
            String value = firstRow.getCell(i).getStringCellValue();
            if("场站".equals(value)){
                tnIndex = i;
                break;
            }
        }

        HashMap<String,String> tagTnMap = new HashMap<>();
        while(true){
            row = sheet.getRow(rowIndex);
            if(row == null) {
                break;
            }
            Cell tagNameCell = row.getCell(0);
            String tagName = tagNameCell.getStringCellValue();
            if(StringUtils.isNotBlank(tagName) && tnIndex >-1){
                Cell tagDescCell = row.getCell(tnIndex);
                String tn = tagDescCell.getStringCellValue();
                if(StringUtils.isNotBlank(tn)){
                    tagTnMap.put(tagName.toLowerCase(),tn);
                }else{
                    tagTnMap.put(tagName.toLowerCase(),"");
                }
            }
            rowIndex++;
        }
        tdEngineService.updateTagTn(dbConfig,tagTnMap);
        return true;
    }

    /**
     * 获取每个数据类型对应的索引
     * @param val
     * @return
     */
    private byte getDataType(String val) {
        if(StrUtil.isNotBlank(val)){
            if(val.toUpperCase().contains("FLOAT")){
                return (byte)0;
            } else if (val.toUpperCase().contains("BOOL")) {
                return (byte)1;
            } else if (val.toUpperCase().contains("DOUBLE")) {
                return (byte)5;
            } else if (val.toUpperCase().contains("INT")) {
                return (byte)2;
            } else if (val.toUpperCase().contains("LONG")) {
                return (byte)3;
            } else if (val.toUpperCase().contains("DWORD")) {
                return (byte)4;
            } else if (val.toUpperCase().contains("STRING")) {
                return (byte)6;
            }
        }

        return (byte)-1;
    }

    private String getTagType(String val) {
        if(StrUtil.isNotBlank(val)) {
            if (val.toUpperCase().contains("FLOAT")) {
                return "FLOAT";
            } else if (val.toUpperCase().contains("BOOL")) {
                return "BOOL";
            } else if (val.toUpperCase().contains("DOUBLE")) {
                return "DOUBLE";
            } else if (val.toUpperCase().contains("INT")) {
                return "INT";
            } else if (val.toUpperCase().contains("LONG")) {
                return "LONG";
            } else if (val.toUpperCase().contains("DWORD")) {
                return "DWORD";
            } else if (val.toUpperCase().contains("STRING")) {
                return "STRING";
            }
        }
        return "UNKNOW";
    }

    private TagType getEnumTagType(String val) {
        if(StrUtil.isNotBlank(val)) {
            if (val.toUpperCase().contains("FLOAT")) {
                return TagType.FLOAT;
            } else if (val.toUpperCase().contains("BOOL")) {
                return TagType.BOOL;
            } else if (val.toUpperCase().contains("DOUBLE")) {
                return TagType.DOUBLE;
            } else if (val.toUpperCase().contains("INT")) {
                return TagType.INT;
            } else if (val.toUpperCase().contains("LONG")) {
                return null;
            } else if (val.toUpperCase().contains("DWORD")) {
                return null;
            } else if (val.toUpperCase().contains("STRING")) {
                return null;
            }
        }
        return null;
    }

    private List<Map>  read(Workbook book) throws IOException{
        List<Map> list= new ArrayList<>();
        for(int m=0;m<book.getNumberOfSheets();m++){
            Sheet sheet = book.getSheetAt(m);
            int rowStart =sheet.getFirstRowNum(); //首行下标
            int rowEnd =sheet.getLastRowNum(); //尾行下标

            Row firstRow =sheet.getRow(rowStart);
            int cellStart = firstRow.getFirstCellNum();  //获取一行中的开始索引
            int cellEnd = firstRow.getLastCellNum();// 获取一行中的结束索引
            Map<Integer,String> keyMap =new HashMap<>();
            for(int j= cellStart;j<cellEnd;j++){
                // 对表头数据进行解析 获取当前单元格中的数据
                String val = getValue(firstRow.getCell(j));
                if(val == null || val.trim().isEmpty()){    //val.trim() 去除空格
                    cellEnd = j;
                    break;
                }
                keyMap.put(j,val);
            }
            if(keyMap.isEmpty()){
                return Collections.emptyList();
            }
            //获取每行JSON对象的值
            List<Map> array = new ArrayList<>();
            //如果首行与尾行相同，表明只有一行，返回表头数据
            if(rowStart == rowEnd){
                Map<String,String> object = new HashMap<>();
                for(int i: keyMap.keySet()){
                    object.put(keyMap.get(i).toString(),"");
                }
                array.add(object);
            }
            //解析表中全部数据
            for(int i= rowStart+1;i<=rowEnd;i++){
                Row eachRow = sheet.getRow(i);
                Map<Object,String> obj =new HashMap<>();
                StringBuffer sb =new StringBuffer();
                for(int k = cellStart;k<cellEnd;k++){
                    if(eachRow != null){
                        String val =getValue(eachRow.getCell(k));
                        sb.append(val);//所有数据添加到里面，用于判断该行是否为空
                        obj.put(keyMap.get(k),val);
                    }
                }
                if(sb.toString().length()>0){
                    array.add(obj);
                }
            }
            Map map = new HashMap();
            map.put("sheet",array);
            list.add(map);
        }
        return list;
    }

    //获取单元格数据
    private String getValue(Cell cell){
        // 空白或空
        if (cell == null || cell.getCellType() == CellType.BLANK ) {
            return "";
        }
        // 布尔值 CELL_TYPE_BOOLEAN
        if (cell.getCellType() == CellType.BOOLEAN) {
            return cell.getBooleanCellValue()+"";
        }
        //  数字 类型
        if (cell.getCellType() == CellType.NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                return df.format(date);
            }
            cell.setCellType(CellType.STRING);
            String val = cell.getStringCellValue()+"";
            val = val.toUpperCase();
            if (val.contains("E")) {
                val = val.split("E")[0].replace(".", "");
            }
            return val;
        }
        //  公式 CELL_TYPE_FORMULA
        if (cell.getCellType() == CellType.FORMULA) {
            return cell.getCellFormula();
        }
        // String类型
        if (cell.getCellType() == CellType.STRING) {
            String val = cell.getStringCellValue();
            if (val == null || val.trim().length() == 0) {
                return "";
            }
            return val.trim();
        }

        //  错误 CELL_TYPE_ERROR
        if(cell.getCellType() == CellType.ERROR){
            return "错误";
        }

        return "";
    }

    //MultipartFile转换成为File
    public File mutipartFile(MultipartFile file){
        File toFile = null;
        if(file.equals("")||file.getSize()<=0){
            file =null;
        }else {
            InputStream InputStream = null;
            try {
                InputStream = file.getInputStream();
                toFile =new File(file.getOriginalFilename());
                inputStreamToFile(InputStream,toFile);
                InputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return toFile;
    }

    //获取流文件
    private static void inputStreamToFile(InputStream inputStream, File file) {
        OutputStream os =null;
        try {
            os = new FileOutputStream(file);
            int bytesRead =0;
            byte[] butter =new byte[1024];
            while((bytesRead = inputStream.read(butter, 0,1024))!=-1){
                os.write(butter,0,bytesRead);
            }
            os.close();
            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 查询表中所有测点表
     * @param showTableDto
     * @return
     */
    public Page<DbsManagetagInfo> showTables(ShowTableDto showTableDto) throws SQLException, IOException {
        DBConfig dbConfig = showTableDto.getDbConfig();
        if(dbConfig != null && StringUtils.isNotBlank(dbConfig.getDbName()) && StringUtils.isNotBlank(dbConfig.getHost())){
            Page<DbsManagetagInfo> page = findPage(showTableDto);
            if(!page.getRecords().isEmpty()){
                List<DbsManagetagInfo> result = getTableDesc(page.getRecords(),dbConfig);
                page.setRecords(result);
            }
            return page;
        }
        return null;
    }

    /**
     * 分页查询
     * @param showTableDto
     * @return
     */
    private Page<DbsManagetagInfo> findPage(ShowTableDto showTableDto) throws SQLException {
            Integer currentPage = showTableDto.getCurrentPage();
            Integer pageSize = showTableDto.getPageSize();
            Page<DbsManagetagInfo> page = new Page<>(currentPage, pageSize);
        List<DbsManagetagInfo> result = new ArrayList<>();
        DBConfig dbConfig = showTableDto.getDbConfig();
        TDEngineDao tdEngineDao = tdEngineService.getTDEngineDao(dbConfig.getHost(), dbConfig.getPort(), dbConfig.getUsername()
                , dbConfig.getPassword(), dbConfig.getDbName());
        tdEngineDao.useDb(dbConfig.getDbName());
        String tn = showTableDto.getTn();
        String sql = "";
        String allSql = "";
        if(tn == null || "".equals(tn) || "''".equals(tn)){
             sql = "select * from information_schema.ins_tables where db_name = '"+dbConfig.getDbName()+"' ";
             allSql = "select count(*) from information_schema.ins_tables where db_name = '"+dbConfig.getDbName()+"' ";
            if(showTableDto.getTableName() != null && showTableDto.getTableName() != ""){
                sql += " and table_name like '%"+showTableDto.getTableName().toLowerCase()+"%' ";
                allSql += " and table_name like '%"+showTableDto.getTableName().toLowerCase()+"%' ";
            }
            sql += " order by table_name ";
            if(currentPage> 0 && pageSize > 0){
                sql += "  limit "+(pageSize>10000?10000:pageSize)+" offset "+(currentPage-1)*pageSize+";";
            }
        }else{
            sql = "select * from information_schema.ins_tags where tag_name = 'tn' and tag_value like '%"+tn+"%' and db_name = '"+dbConfig.getDbName()+"' ";
            allSql = "select count(*) from information_schema.ins_tags where tag_name = 'tn' and tag_value like '%"+tn+"%' and db_name = '"+dbConfig.getDbName()+"' ";
            if(showTableDto.getTableName() != null && showTableDto.getTableName() != ""){
                sql += " and table_name like '%"+showTableDto.getTableName().toLowerCase()+"%' ";
                allSql += " and table_name like '%"+showTableDto.getTableName().toLowerCase()+"%' ";
            }
            sql += " order by table_name ";
            if(currentPage> 0 && pageSize > 0){
                sql += "  limit "+(pageSize>10000?10000:pageSize)+" offset "+(currentPage-1)*pageSize+";";
            }
        }


        try(Connection connection = tdEngineDao.getConnection(); Statement statement = connection.createStatement();){
            try(ResultSet resultSet = statement.executeQuery(allSql)){
                if(resultSet.next()){
                    page.setTotal(resultSet.getLong(1));
                }

                List<List<String>> list = tdEngineDao.executeSQL(sql,0);
                if(list != null && list.size()>0) {
                    Map<Integer, String> columnMap = new HashMap<>();
                    JSONObject jsonObject = null;
                    for (int i = 0; i < list.size(); i++) {
                        if (i == 0) {
                            List<String> columnList = list.get(0);
                            int index = 0;
                            for (String columnName : columnList) {
                                columnMap.put(index, columnName);
                                index++;
                            }
                        } else {
                            jsonObject = new JSONObject();
                            List<String> columnList = list.get(i);
                            int index = 0;
                            for (String val : columnList) {
                                String columnName = columnMap.get(index);
                                jsonObject.set(columnName, val);
                                index++;
                            }
                            DbsManagetagInfo myObject = new DbsManagetagInfo();

                            myObject.setTagName(jsonObject.getStr("table_name"));
                            myObject.setTagDesc(jsonObject.getStr("table_comment"));
                            myObject.setTagId(jsonObject.getStr("uid"));
                            String sTableName = jsonObject.getStr("stable_name");
                            myObject.setTagType(sTableName);
                            myObject.setDataType(getDataType(sTableName));
                            myObject.setDbIp(dbConfig.getHost());
                            myObject.setDbName(dbConfig.getDbName());
                            myObject.setTagStatue("已同步");

                            myObject.setTn(jsonObject.getStr("tn"));
                            myObject.setTa(jsonObject.getStr("ta"));
                            // 将JSON字符串转换为MyObject对象
                            // MyObject是你自定义的类，包含name和age属性及其对应的getter和setter方法
                            result.add(myObject);
                        }
                    }
                }
            }catch (Exception e){
                throw new RuntimeException(e);
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        page.setRecords(result);
        return page;
    }

    private List<DbsManagetagInfo> getTableDesc(List<DbsManagetagInfo> result,DBConfig dbConfig) throws SQLException {
        List<DbsManagetagInfo> returnList = new ArrayList<>();
        TDEngineDao tdEngineDao = tdEngineService.getTDEngineDao(dbConfig.getHost(), dbConfig.getPort(), dbConfig.getUsername()
                , dbConfig.getPassword(), dbConfig.getDbName());
        tdEngineDao.useDb(dbConfig.getDbName());
        if(result.size()>1000){
            int size = result.size()/1000;
            for(int i = 0;i<size;i++){
                List<DbsManagetagInfo> valList = getTableDesc(result.subList(i*1000,(i+1)*1000),dbConfig);
                if(!valList.isEmpty()){
                    returnList.addAll(valList);
                }
            }
            if(result.size()%1000 >  0){
                List<DbsManagetagInfo> valList = getTableDesc(result.subList(size*1000,result.size()),dbConfig);
                if(!valList.isEmpty()){
                    returnList.addAll(valList);
                }
            }
        }else{
            String searchDesc = getDescSQL(result, dbConfig);
            List<List<String>> list = tdEngineDao.executeSQL(searchDesc,0);
            Map<String, JSONObject> resultMap = new HashMap<>();
            Map<String,String> tiMap = new HashMap<>();
            Map<String,String> tnMap = new HashMap<>();
            Map<String,String> taMap = new HashMap<>();
            if(list != null && !list.isEmpty()) {
                for (int i = 0; i < list.size(); i++) {
                    if (i == 0) {
                    } else {
                        List<String> columnList = list.get(i);
                        String tableName = columnList.get(0);
                        String ti = columnList.get(1);
                        String tn = columnList.get(2);
                        String ta = columnList.get(3);
                        tiMap.put(tableName,ti);
                        tnMap.put(tableName,tn);
                        taMap.put(tableName,ta);
                        try{
                            if(StringUtils.isNotBlank(ti) && JSONUtil.isTypeJSON(ti)){
                                JSONObject jsonObject = JSONUtil.parseObj(ti);
                                resultMap.put(tableName,jsonObject);
                            }

                        }catch (Exception e){
                            log.error("JSON反序列化失败",e);
                        }
                    }
                }
                for(DbsManagetagInfo dto : result){
                    if(resultMap.containsKey(dto.getTagName())){
                        JSONObject jsonObject = resultMap.get(dto.getTagName());
                        String type = jsonObject.getStr("tagType");
                        String desc = jsonObject.getStr("tagDesc");
                        dto.setTagDesc(desc);
                        dto.setTagType(type);
                        dto.setMaxValue(Float.parseFloat(StringUtils.isNotBlank(jsonObject.getStr("fmaxVal"))?jsonObject.getStr("fmaxVal"):"0"));
                        dto.setMinValue(Float.parseFloat(StringUtils.isNotBlank(jsonObject.getStr("fminVal"))?jsonObject.getStr("fminVal"):"0"));
                        dto.setTi(tiMap.get(dto.getTagName()));
                        if(tnMap.containsKey(dto.getTagName())){
                            dto.setTn(tnMap.get(dto.getTagName()));
                        }
                        if(taMap.containsKey(dto.getTagName())){
                            dto.setTa(taMap.get(dto.getTagName()));
                        }
                    }
                    returnList.add(dto);
                }
                resultMap.clear();
                tiMap.clear();
            }
        }
        return returnList;
    }

    private static String getDescSQL(List<DbsManagetagInfo> result, DBConfig dbConfig) {
        String tbnameSql = "(";
        for(DbsManagetagInfo dto : result){
            tbnameSql += "'"+dto.getTagName()+"',";
        }
        tbnameSql = tbnameSql.substring(0,tbnameSql.length()-1)+")";
        String searchDesc = "select * from (select distinct(tbname),ti,tn,ta from "+ dbConfig.getDbName()+".tfloat where tbname in "+tbnameSql +
                " union all\n" +
                " select distinct(tbname),ti,tn,ta from "+ dbConfig.getDbName()+".tdouble where tbname in "+tbnameSql +
                " union all\n" +
                " select distinct(tbname),ti,tn,ta from "+ dbConfig.getDbName()+".tint where tbname in "+tbnameSql +
                " union all\n" +
                " select distinct(tbname),ti,tn,ta from "+ dbConfig.getDbName()+".tbool  where tbname in "+tbnameSql+")";

        return searchDesc;
    }

    private static String[] getAllDescSQL(DBConfig dbConfig) {
        String searchDesc = "select distinct(tbname),ti,tn,ta from (select distinct(tbname),ti,tn,ta from "+ dbConfig.getDbName()+".tfloat \n" +
                " union all\n" +
                " select distinct(tbname),ti,tn,ta from "+ dbConfig.getDbName()+".tdouble \n" +
                " union all\n" +
                " select distinct(tbname),ti,tn,ta from "+ dbConfig.getDbName()+".tint \n" +
                " union all\n" +
                " select distinct(tbname),ti,tn,ta from "+ dbConfig.getDbName()+".tbool )";
        String[] arr = new String[4];
        arr[0] = "select distinct(tbname),ti,tn,ta from "+ dbConfig.getDbName()+".tfloat";
        arr[1] = "select distinct(tbname),ti,tn,ta from "+ dbConfig.getDbName()+".tdouble";
        arr[2] = "select distinct(tbname),ti,tn,ta from "+ dbConfig.getDbName()+".tint";
        arr[3] = "select distinct(tbname),ti,tn,ta from "+ dbConfig.getDbName()+".tbool";
        return arr;
    }

    /**
     * 查询所有的表信息
     * @param showTableDto
     * @return
     */
    private List<DbsManagetagInfo> getAllTableInfo(ShowTableDto showTableDto) throws SQLException {
        List<DbsManagetagInfo> result = new ArrayList<>();
        DBConfig dbConfig = showTableDto.getDbConfig();
        TDEngineDao tdEngineDao = tdEngineService.getTDEngineDao(dbConfig.getHost(), dbConfig.getPort(), dbConfig.getUsername()
                , dbConfig.getPassword(), dbConfig.getDbName());
        tdEngineDao.useDb(dbConfig.getDbName());
//        String sql = "select * from information_schema.ins_tables where db_name = '"+dbConfig.getDbName()+"' ";
//        if(showTableDto.getTableName() != null && showTableDto.getTableName() != ""){
//            sql += " and table_name like '%"+showTableDto.getTableName()+"%' ";
//        }
//        sql += " order by table_name ";
//        if(StringUtils.isNotBlank(showTableDto.getTableName())){
//            sql += "and table_name like '%"+showTableDto.getTableName()+"%'";
//        }


//        String sql = "select * from (";
//        for(int i =0;i<4;i++){
//            String stableName = "tfloat";
//            if(i == 0){
//                stableName= "tfloat";
//            }else if(i == 1){
//                stableName= "tbool";
//            }else if(i == 2){
//                stableName= "tint";
//            }else if(i == 3){
//                stableName= "tdouble";
//            }
//            String v ="SELECT TBNAME,tn,ta,'"+stableName+"' as stb FROM "+dbConfig.getDbName()+"."+stableName;
//            if(showTableDto.getTableName() != null && showTableDto.getTableName() !="" && showTableDto.getTableName() != "''"){
//                v += " where tbname like '%"+showTableDto.getTableName()+"%' ";
//            }
//            v+=" group by tbname ";
//            if(i != 3){
//                sql += v+" union all ";
//            }else{
//                sql += v+")";
//            }
//
//        }

        String sql = "show tables ";
        if(StringUtils.isNotBlank(showTableDto.getTableName())) {
            sql += "like '%" + showTableDto.getTableName() + "%'";
        }

        List<List<String>> list = tdEngineDao.executeSQL(sql,0);
        if(list != null && list.size()>0) {
            Map<Integer, String> columnMap = new HashMap<>();
            JSONObject jsonObject = null;
            for (int i = 0; i < list.size(); i++) {
                if (i == 0) {
                    List<String> columnList = list.get(0);
                    int index = 0;
                    for (String columnName : columnList) {
                        columnMap.put(index, columnName);
                        index++;
                    }
                } else {
                    jsonObject = new JSONObject();
                    List<String> columnList = list.get(i);
                    int index = 0;
                    for (String val : columnList) {
                        String columnName = columnMap.get(index);
                        jsonObject.set(columnName, val);
                        index++;
                    }
                    DbsManagetagInfo myObject = new DbsManagetagInfo();
//                    myObject.setTagName(jsonObject.getString("tbname"));
//                    myObject.setTagDesc(null);
//                    myObject.setTagId(null);
//                    String sTableName = jsonObject.getString("stb");
//                    myObject.setTagType(sTableName);
//                    myObject.setDataType(getDataType(sTableName));
//                    myObject.setDbIp(dbConfig.getHost());
//                    myObject.setDbName(dbConfig.getDbName());
//                    myObject.setTagStatue("已同步");
//                    myObject.setTn(jsonObject.getString("tn"));
//                    myObject.setTa(jsonObject.getString("ta"));

                    myObject.setTagName(jsonObject.getStr("table_name"));
                    myObject.setTagDesc(jsonObject.getStr("table_comment"));
                    myObject.setTagId(jsonObject.getStr("uid"));
                    String sTableName = jsonObject.getStr("stable_name");
                    myObject.setTagType(sTableName);
                    myObject.setDataType(getDataType(sTableName));
                    myObject.setDbIp(dbConfig.getHost());
                    myObject.setDbName(dbConfig.getDbName());
                    myObject.setTagStatue("已同步");

                    myObject.setTn(jsonObject.getStr("tn"));
                    myObject.setTa(jsonObject.getStr("ta"));
                    // 将JSON字符串转换为MyObject对象
                    // MyObject是你自定义的类，包含name和age属性及其对应的getter和setter方法
                    result.add(myObject);
                }
            }
        }
        //蒋result集合进行排序与截取
        Collections.sort(result, new Comparator<DbsManagetagInfo>() {
            @Override
            public int compare(DbsManagetagInfo o1, DbsManagetagInfo o2) {

                return o1.getTagName().compareTo(o2.getTagName());
            }
        });
        return result;
    }

    /**
     * 批量删除测点信息
     * @param list
     * @return
     */
    public String deleteTables(List<DbsManagetagInfo> list) {
        if(list != null && !list.isEmpty()){
            List<String> idList = new ArrayList<>();
            for(DbsManagetagInfo dbsManagetagInfo : list){
                idList.add(dbsManagetagInfo.getId());
            }
            managerPointsInfoMapper.deleteBatchIds(idList);
            return "删除成功";
        }
        return "";
    }

    /**
     * 表格信息的导出
     * @return
     */
    public Workbook getExport(ExportTableDto exportTableDto) throws SQLException, IOException {
        Workbook workbook = new SXSSFWorkbook(1000);


        DBConfig dbConfig = exportTableDto.getDbConfig();
        List<DbsManagetagInfo> dataList = new ArrayList<>();
        if(exportTableDto.getCommand().equalsIgnoreCase("all")){
            ShowTableDto showTableDto = new ShowTableDto();
            showTableDto.setDbConfig(dbConfig);
            dataList = getAllTableInfo(showTableDto);
        }else{
            ShowTableDto showTableDto = new ShowTableDto();
            showTableDto.setTableName(exportTableDto.getTableName());
            showTableDto.setDbConfig(dbConfig);
            dataList = getAllTableInfo(showTableDto);
        }

        if(!dataList.isEmpty()){
            Map<String,DbsManagetagInfo> tableNameMap = new HashMap<>();
            for(DbsManagetagInfo dbsManagetagInfo : dataList){
                tableNameMap.put(dbsManagetagInfo.getTagName(),dbsManagetagInfo);
            }
            String[] arr = getAllDescSQL(dbConfig);
            TDEngineDao tdEngineDao = tdEngineService.getTDEngineDao(dbConfig.getHost(), dbConfig.getPort(), dbConfig.getUsername()
                    , dbConfig.getPassword(), dbConfig.getDbName());
            tdEngineDao.useDb(dbConfig.getDbName());
            downloadCount = 0;
            List<DbsManagetagInfo> list = new ArrayList<>();
            Timer timer = new Timer();
            TimerTask task = new TimerTask() {
                @Override
                public void run() {
                    System.out.println("数据导出数量：" + list.size());
                }
            };
            // 延迟5秒后执行，每10秒重复一次
            timer.schedule(task, 5000, 10000);
            Row row = null;
            JSONObject jsonObject = null;
            Sheet sheet = getSheet(workbook,"data0");
            for(String searchDesc : arr){
                try (ResultSet rs = tdEngineDao.executeQuery(searchDesc)) {
                    if(rs != null){
                        while (rs.next()) {
                            String tableName = rs.getString(1);
                            if(tableNameMap.containsKey(tableName)){
                                DbsManagetagInfo dbsManagetagInfo = tableNameMap.get(tableName);
                                tableNameMap.remove(tableName);
                                String ti = rs.getString(2);
                                String tn = rs.getString(3);
                                String ta = rs.getString(4);
                                try{
                                    if(StringUtils.isNotBlank(ti) && JSONUtil.isTypeJSON(ti)){
                                        jsonObject = JSONUtil.parseObj(ti);
                                        String type = jsonObject.getStr("tagType");
                                        String desc = jsonObject.getStr("tagDesc");
                                        dbsManagetagInfo.setTagDesc(desc);
                                        dbsManagetagInfo.setTagType(type);
                                        dbsManagetagInfo.setMaxValue(Float.parseFloat(StringUtils.isNotBlank(jsonObject.getStr("fmaxVal"))?jsonObject.getStr("fmaxVal"):"0"));
                                        dbsManagetagInfo.setMinValue(Float.parseFloat(StringUtils.isNotBlank(jsonObject.getStr("fminVal"))?jsonObject.getStr("fminVal"):"0"));
                                        dbsManagetagInfo.setPointNum(Byte.parseByte(StringUtils.isNotBlank(jsonObject.getStr("pointNum"))?jsonObject.getStr("pointNum"):"0"));
                                        dbsManagetagInfo.setSzUnit(jsonObject.getStr("unit"));
                                    }
                                    dbsManagetagInfo.setTi(ti);
                                    dbsManagetagInfo.setTn(tn);
                                    dbsManagetagInfo.setTa(ta);
                                    list.add(dbsManagetagInfo);
                                }catch (Exception e){
                                    log.error("JSON反序列化失败",e);
                                }
                            }
                        }
                    }
                }catch (Exception e){
//                    timer.cancel();
                }
            }
            if (list != null && !list.isEmpty()) {
                for(DbsManagetagInfo dbsManagetagInfo : list){
                    row = sheet.createRow((downloadCount%500000) + 1);
                    row.createCell(0).setCellValue(dbsManagetagInfo.getTagName());
                    row.createCell(1).setCellValue(dbsManagetagInfo.getTagDesc());
                    row.createCell(2).setCellValue(dbsManagetagInfo.getTagType());
                    row.createCell(3).setCellValue(dbsManagetagInfo.getTagStatue());
                    row.createCell(4).setCellValue(dbsManagetagInfo.getTn());
                    row.createCell(5).setCellValue(dbsManagetagInfo.getTa());
                    row.createCell(6).setCellValue(dbsManagetagInfo.getPointNum());
                    row.createCell(7).setCellValue(dbsManagetagInfo.getSzUnit());
                    row.createCell(8).setCellValue(dbsManagetagInfo.getMaxValue());
                    row.createCell(9).setCellValue(dbsManagetagInfo.getMinValue());
                    downloadCount++;
                    if(downloadCount % 500000 == 0 && downloadCount>0){
                        sheet = getSheet(workbook,"data"+(downloadCount/500000));
                        ((SXSSFSheet) sheet).flushRows(10000);
                    }else{
                        // 定期清理旧行以释放内存
                        if (downloadCount % 10000 == 0 && downloadCount > 0) {
                            ((SXSSFSheet) sheet).flushRows(10000);
                        }
                    }
                }
            }
            timer.cancel();
        }
        return workbook;
    }

    /**
     * 创建导出的sheet页
     * @param workbook
     * @return
     */
    private Sheet getSheet(Workbook workbook,String sheetName) {
        Sheet sheet = workbook.createSheet(sheetName);

        // 创建标题行
        Row titleRow = sheet.createRow(0);
        Cell titleCell0 = titleRow.createCell(0);
        titleCell0.setCellValue("表名");
        Cell titleCell1 = titleRow.createCell(1);
        titleCell1.setCellValue("描述");
        Cell titleCell2 = titleRow.createCell(2);
        titleCell2.setCellValue("类型");
        Cell titleCell3 = titleRow.createCell(3);
        titleCell3.setCellValue("状态");
        Cell titleCell4 = titleRow.createCell(4);
        titleCell4.setCellValue("场站");
        Cell titleCell5 = titleRow.createCell(5);
        titleCell5.setCellValue("ta");

        Cell titleCell6 = titleRow.createCell(6);
        titleCell6.setCellValue("小数位数");
        Cell titleCell7 = titleRow.createCell(7);
        titleCell7.setCellValue("单位");
        Cell titleCell8 = titleRow.createCell(8);
        titleCell8.setCellValue("量程上限");
        Cell titleCell9 = titleRow.createCell(9);
        titleCell9.setCellValue("量程下限");
        return sheet;
    }

    /**
     * 同步数据
     * @param showTableDto
     */
    public void syncDBToTDengine(ShowTableDto showTableDto) throws SQLException {
        DBConfig dbConfig = showTableDto.getDbConfig();
        if(dbConfig != null && StringUtils.isNotBlank(dbConfig.getDbName()) && StringUtils.isNotBlank(dbConfig.getHost())){
            LambdaQueryWrapper<DbsManagetagInfo> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(DbsManagetagInfo::getDbName, dbConfig.getDbName());
            wrapper.eq(DbsManagetagInfo::getDbIp, dbConfig.getHost());
            if(StringUtils.isNotBlank(showTableDto.getTableName())){
                wrapper.like(DbsManagetagInfo::getTagName, showTableDto.getTableName());
            }
            List<DbsManagetagInfo> dataList = managerPointsInfoMapper.selectList(wrapper);
            if(dataList != null && !dataList.isEmpty()){
                // 填充数据
                List<TagInfo> tagInfoList = new ArrayList<>();
                TagInfo tagInfo = null;
                for (int i = 0; i < dataList.size(); i++) {
                    tagInfo = new TagInfo();
                    DbsManagetagInfo dbsManagetagInfo = dataList.get(i);
                    dbsManagetagInfo.setTagStatue("已同步");
                    managerPointsInfoMapper.updateById(dbsManagetagInfo);
                    tagInfo.setTagId(Long.parseLong(dbsManagetagInfo.getId()));
                    tagInfo.setTagName(dbsManagetagInfo.getTagName());
                    TagType tagType = getEnumTagType(dbsManagetagInfo.getTagType());
                    if(tagType != null){
                        tagInfo.setTagType(tagType);
                        tagInfo.setTags(tagInfo.toString());
                        tagInfoList.add(tagInfo);
                    }
                }
                TDEngineDao tdEngineDao = tdEngineService.getTDEngineDao(dbConfig.getHost(), dbConfig.getPort(), dbConfig.getUsername()
                        , dbConfig.getPassword(), dbConfig.getDbName());
                tdEngineDao.useDb(dbConfig.getDbName());
                tdEngineDao.createTables(tagInfoList);
            }
        }
    }
}
